package com.gdssoft.pdaweb.dao.am.impl;
/*
 *  捷达世软件（深圳）有限公司
 *  Copyright (C) 2008-2010 ShenZhen JieDaShi Software Limited Company.
*/
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import com.gdssoft.core.dao.impl.BaseDaoImpl;
import com.gdssoft.core.util.DateUtil;
import com.gdssoft.pdaweb.dao.am.PdaAmMeterListDao;
import com.gdssoft.pdaweb.model.Excel;
import com.gdssoft.pdaweb.model.am.PdaAmMeter;
import com.gdssoft.pdaweb.model.am.PdaAmMeterList;


public class PdaAmMeterListDaoImpl extends BaseDaoImpl<PdaAmMeterList> implements PdaAmMeterListDao{

	public PdaAmMeterListDaoImpl() {
		super(PdaAmMeterList.class);
	}

	private SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");
	
	/**
	 * sqlAndArgsForQueryOrCount 得到一致的用于查询记录数和记录的sql以及参数list
	 * @author F3229233 2013-1-15 下午3:21:50 
	 * @param pl
	 * @return Map(包含从from开始的sql及条件参数list)
	 */
	private Map<String,Object> sqlAndArgsForQueryOrCount(PdaAmMeterList pl){
		List<Object> args = new ArrayList<Object>();
		
		StringBuffer sql = new StringBuffer(" FROM PDA_AM_METER_LIST pl" +
			" JOIN PDA_AM_METER pm ON pl.AM_ID=pm.AM_ID" +
			" JOIN PDA_APARMENT pa ON pm.APARMENT_ID=pa.APARMENT_ID WHERE pl.MONTH=? ");
		
		String monthStr = pl.getMonth()==null?sdf.format(new Date()):pl.getMonth();
		pl.setMonth(monthStr);
		args.add(pl.getMonth());
		
		if(pl.getStatus()!=null && !pl.getStatus().isEmpty()){	//条件：状态
			if(pl.getStatus().equals("0")||pl.getStatus().equals("1")){
				sql.append("AND pl.STATUS=? ");
				args.add(pl.getStatus());
			}else{
				sql.append("AND pl.STATUS IN (2,3,4) ");
			}
		}
		if(pl.getAmId()!=null && !pl.getAmId().isEmpty()){	
			sql.append("AND pl.AM_ID LIKE ? ");
			args.add(pl.getAmId()==null?"%":pl.getAmId()+"%");
		}
		if(pl.getPdaAmMeter()!=null){
			if(pl.getPdaAmMeter().getClientId()!=null && !pl.getPdaAmMeter().getClientId().isEmpty()){
				sql.append("AND pm.CLIENT_ID LIKE ? ");
				args.add(pl.getPdaAmMeter().getClientId()+"%");
			}
			if(pl.getPdaAmMeter().getDistrictId()!=null && !pl.getPdaAmMeter().getDistrictId().isEmpty()){
				sql.append("AND pm.DISTRICT_ID LIKE ? ");
				args.add(pl.getPdaAmMeter().getDistrictId()+"%");
			}
			if(pl.getPdaAmMeter().getBuildingId()!=null && !pl.getPdaAmMeter().getBuildingId().isEmpty()){
				sql.append("AND pm.BUILDING_ID LIKE ? ");
				args.add(pl.getPdaAmMeter().getBuildingId()+"%");
			}
			if(pl.getPdaAmMeter().getAparmentId()!=null && !pl.getPdaAmMeter().getAparmentId().isEmpty()){
				sql.append("AND pm.APARMENT_ID LIKE ?");
				args.add(pl.getPdaAmMeter().getAparmentId()+"%");
			}
		}
		
		Map<String,Object> map = new HashMap<String,Object>();
		map.put("SQL", sql);
		map.put("ARGS", args);
		return map;
	}
	
	@Override
	public int queryCount(PdaAmMeterList pl){
		Map sqlMap = sqlAndArgsForQueryOrCount(pl);
		String sql = "SELECT COUNT(1) " + sqlMap.get("SQL").toString();
		List<Object> args = (List<Object>)sqlMap.get("ARGS");		
		return jdbcTemplate.queryForInt(sql,args.toArray());					
	}
	
	@SuppressWarnings("rawtypes")
	@Override
	public List<PdaAmMeterList> query(PdaAmMeterList pl,int start,int to) {
		List<PdaAmMeterList> list = new ArrayList<PdaAmMeterList>();
		
		String monthStr = pl.getMonth()==null?sdf.format(new Date()):pl.getMonth();
		pl.setMonth(monthStr);
		
		Map sqlMap = sqlAndArgsForQueryOrCount(pl);
		
		String sql = "SELECT B.* FROM (SELECT ROWNUM rn, A.* FROM(" 
				+ "SELECT nvl(pll.DEGREE,-1) AS LASTDEGREE,pl.* FROM PDA_AM_METER_LIST pll RIGHT JOIN ("
				+ "SELECT pl.ID,pl.AM_ID,pl.DEGREE,pm.DISTRICT_ID,pm.BUILDING_ID,pm.APARMENT_ID,pa.NAME,pl.STATUS,pl.RECORDER_ID"
				+ sqlMap.get("SQL").toString()
				+") pl on pl.AM_ID = pll.AM_ID and pll.MONTH=?"
				+") A WHERE ROWNUM <= "+to+") B WHERE rn > "+start;

		List<Object> args = (List<Object>)sqlMap.get("ARGS");
		args.add(DateUtil.getLastMonthStr(monthStr));
		
		try{
			List rows = jdbcTemplate.queryForList(sql,args.toArray());
			Iterator it = rows.iterator();
			while(it.hasNext()) {
			    Map map = (Map) it.next();
			    PdaAmMeterList newpl = new PdaAmMeterList();
			    newpl.setPdaAmMeter(new PdaAmMeter());
			    newpl.setMonth(monthStr);
			    newpl.setId(((BigDecimal)map.get("ID")).longValue());
			    newpl.setAmId(map.get("AM_ID").toString());
			    newpl.setStatus(map.get("STATUS").toString());
			    newpl.setRecorderId(map.get("RECORDER_ID").toString());
//			    newpl.setLastDegree(((BigDecimal)map.get("LASTDEGREE")).longValue());
			    newpl.setDegree(((BigDecimal)map.get("DEGREE")).longValue());			    
			    newpl.getPdaAmMeter().setDistrictId(map.get("DISTRICT_ID").toString());
			    newpl.getPdaAmMeter().setBuildingId(map.get("BUILDING_ID").toString());
			    newpl.getPdaAmMeter().setAparmentId(map.get("APARMENT_ID").toString());
			    newpl.getPdaAmMeter().setAparmentName(map.get("NAME").toString());
			    list.add(newpl);
			}
		}catch(Exception e){
			logger.debug("PdaAmMeterList query error:"+e.getMessage());			
		}
		return list;
	}

	@Override
	public int importExcel(List<Excel> excelList){
	  try{
		List<Object> args = new ArrayList<Object>();
		int rowNum = 0;
		for(Excel excel : excelList)
		{
			args.add(excel.getCurDegree());
			args.add(excel.getId());
			args.add(excel.getCurMonth());
			
			StringBuffer sql = new StringBuffer("insert PDA_AM_METER_LIST set degree = ? " +
					"where am_id = ? and month = ?");
			
			try{
			   rowNum += jdbcTemplate.update(sql.toString(),args.toArray());
			  
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		System.out.println("导入的行数为："+rowNum);
		return rowNum;
		}
		catch(Exception e){
					
		}
		return 0;
	}
	@Override
	public boolean checkAmidExist(String amId){
		int ret = jdbcTemplate.queryForInt("SELECT COUNT(*) FROM PDA_AM_METER pl WHERE pl.AM_ID = '"+amId+"' and type = '2'" );
		if(ret > 0){
			return true;
		}
		return false;
	}

	@Override
	public boolean recodExist(String amId, String importMonth){
		String sql = "SELECT COUNT(*) FROM PDA_AM_METER_LIST pl WHERE pl.AM_ID = '"+amId+
				"' AND pl.MONTH = '"+importMonth+"'";
		int ret = jdbcTemplate.queryForInt(sql);
		if(ret > 0)
		{
			return true;
		}
		return false;
	}
	
	@Override
	public Long getDegreeByTime(String amId,String month){
		String sql = "select nvl(degree,-1) from PDA_AM_METER_LIST where am_id = '"+amId+"' and month = '"+month+"'";
		try{
			BigDecimal bd = (BigDecimal)jdbcTemplate.queryForObject(sql,BigDecimal.class);
			long degree = bd.longValue();
			return degree;
		}catch(Exception e){
			logger.error("查询出错或记录不存在！");
			return -1L;
		}
	}
	

	@Override
	public boolean check(String ids, String status) {
		int count = jdbcTemplate.update("UPDATE PDA_AM_METER_LIST SET STATUS = "+status +" WHERE ID IN ("+ids+")");
		if(count==ids.split(",").length){
			return true;
		}
		return false;
	}
	
	@Override
	public PdaAmMeterList getAmListByMeterIDAndMonth(String meterID, String month){
		String hql="from PdaAmMeterList where amId=? and month=?";
		return (PdaAmMeterList) this.findUnique(hql,new String[]{meterID, month});
	}

	@Override
	public boolean saveFixedUsage(PdaAmMeterList pdaAmMeterList) {
		String sql = "";
		if(pdaAmMeterList.getRemark().equals("")){
			sql = "update pda_am_meter_list set status=0, fixedusage="
					+pdaAmMeterList.getFixedUsage()
					+" where id="
					+pdaAmMeterList.getId();
		}else{
			sql = "update pda_am_meter_list set status=0, remark='"
					+ pdaAmMeterList.getRemark()
					+ "', fixedusage="
					+pdaAmMeterList.getFixedUsage()
					+" where id="
					+pdaAmMeterList.getId();
		}
		
		int count = jdbcTemplate.update(sql);
		if(count > 0){
			return true;
		}
		return false;
	}
}
